package com.xwdeshj.dal;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xwdeshj.utils.DateUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;

/**
 * 景区店铺基本信息—SQL类
 * 因jdbcTemplate 加载有相应的顺序，故在该类注解就会报错。
 */
public class Dal_XwSceBasicinfo {

    /**
     * 农家乐信息列表查询
     */
    public JSONObject gettourinfolist(Integer pagesize, Integer pageindex, String starttime, String endtime,
                                      String searchval, String searchkey, String sortfild, String sortway,
                                      String type, String grade, JdbcTemplate jdbcTemplate){
        JSONObject result = new JSONObject();

        String sql = "SELECT\n" +
                "  a.business_type         AS businessTypeid,\n" +
                "  a.scenicType_desc             AS businessTypeName,\n" +
                "  b.business_name         AS business_name,\n" +
                "  b.business_CompanyName  AS CompanyName,\n" +
                "  b.business_Legal        AS Legal,\n" +
                "  b.business_tel          AS tel,\n" +
                "  a.business_encryptId    AS business_encryptId,\n" +
                "  a.scenic_encryptId        AS scenic_encryptId,\n" +
                "  a.scenic_grade         AS grade ,\n" +
                "  a.scenic_city             AS city,\n" +
                "  a.scenic_district         AS district,\n" +
                "  a.scenic_address          AS address,\n" +
                "  a.scenic_Ticket          AS ticket,\n" +
                "  a.scenic_createtime       AS createtime\n" +
                "\n" +
                "FROM xw_scenic_basicinfo AS a\n" +
                "  LEFT JOIN xw_business_info b ON a.scenic_encryptId = b.sec_encryptId\n" +
                "                                  AND a.business_encryptId = b.business_encryptId ";
        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");
        //时间
        if (!starttime.equals("")) {
            starttime += " 00.00.01";
            endtime += " 23.59.59";
            sb.append(" and a.scenic_createtime >= '" + starttime + "' and a.scenic_createtime <= '" + endtime + "' ");
        }
        //店铺类型
        if (!type.equals("")) {
            sb.append(" and a.scenicType_desc in('" + type + "') ");
        }
        //景区等级
        if (!grade.equals("")) {
            sb.append(" and a.scenic_grade in('" + grade + "') ");
        }

        //搜索内容查询
        if (!searchval.equals("")){
            sb.append(" and ( b."+searchkey+" like '%" +searchval + "%' )");
        }
        /**
         * 只显示已审核通过记录的商家记录
         * 0：未审核
         * 1：已通过
         * 2：已拒绝
         */
        sb.append(" and b.business_state in('1') ");
        //排序
        if (!sortfild.equals("")) {
            sb.append(" order by a." + sortfild + " " + sortway + " ");
        }

        try {

            sql = sql + " where " + sb.toString() + " limit " + (pageindex - 1) * pagesize + "," + pagesize;
            //分页SQL
            String sql2 = "SELECT\n" +
                    "  count(*)\n" +
                    "FROM xw_scenic_basicinfo AS a\n" +
                    "  LEFT JOIN xw_business_info b ON a.scenic_encryptId = b.sec_encryptId\n" +
                    "                                  AND a.business_encryptId = b.business_encryptId\n" +
                    " WHERE "+ sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
            SqlRowSet rs2 = jdbcTemplate.queryForRowSet(sql2);
            result.put("status", 0);
            result.put("msg", "ok");
            while (rs2.next()) {
                Integer count = rs2.getInt(1);
                result.put("recordcount", count);
                if (count % pagesize == 0) {
                    result.put("pagecount", count / pagesize);
                } else {
                    result.put("pagecount", count / pagesize + 1);
                }
            }

            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject object = new JSONObject();
                object.put("businessTypeid", (rs.getString("businessTypeid") == null ? "" : rs.getString("businessTypeid")));
                object.put("businessTypeName", (rs.getString("businessTypeName") == null ? "" : rs.getString("businessTypeName")));
                object.put("business_name", (rs.getString("business_name") == null ? "" : rs.getString("business_name")));
                object.put("CompanyName", rs.getString("CompanyName"));
                object.put("Legal", rs.getString("Legal"));
                object.put("tel", (rs.getString("tel") == null ? "" : rs.getString("tel")));
                object.put("business_encryptId", (rs.getString("business_encryptId") == null ? "" : rs.getString("business_encryptId")));
                object.put("scenic_encryptId", (rs.getString("scenic_encryptId") == null ? "" : rs.getString("scenic_encryptId")));
                object.put("grade", (rs.getString("grade") == null ? "" : rs.getString("grade")));
                object.put("city", (rs.getString("city") == null ? "" : rs.getString("city")));
                object.put("district", (rs.getString("district") == null ? "" : rs.getString("district")));
                object.put("address", (rs.getString("address")==null?"":rs.getString("address")));
                object.put("ticket", (rs.getString("ticket") == null ? "" : rs.getString("ticket")));
                object.put("createtime", DateUtil.DateToString(rs.getDate("createtime")));
                jsonArray.add(object);
            }
            result.put("list", jsonArray);


        }catch (Exception e){
            e.printStackTrace();
            result.put("status", 40002);
            result.put("msg", e);
        }

        return result;
    }


    /**
     *  景区——商家详情——获取农家乐基本信息（一条）
     */
    public JSONObject getfarminfoOne(String business_encryptId, String sceinc_encryptId, JdbcTemplate jdbcTemplate){
        JSONObject result = new JSONObject();

        String sql = "SELECT\n" +
                "  a.business_encryptId        AS business_encryptId,\n" +
                "  a.scenic_encryptId          AS scenic_encryptId,\n" +
                "  b.business_name             AS business_name,\n" +
                "  a.scenicType_desc           AS scenictypeName,\n" +
                "  a.scenicType_encryptId      AS scenicType_encryptId,\n" +
                "  b.business_tel              AS tel,\n" +
                "  b.business_Legal            AS business_Legal,\n" +
                "  b.business_CompanyName      AS CompanyName,\n" +
                "  b.business_create           AS business_create,\n" +
                "  b.business_license          AS business_license,\n" +
                "  b.business_Credit           AS business_Credit,\n" +
                "  a.scenic_city               AS city,\n" +
                "  a.scenic_district           AS district,\n" +
                "  a.scenic_Develop_time       AS develop_time,\n" +
                "  a.scenic_Ticket             AS scenic_ticket,\n" +
                "  a.scenic_address            AS scenic_address,\n" +
                "\n" +
                " a.scenic_views               AS scenic_views,\n"+
                "  a.scenic_score              AS scenic_score,\n" +
                "  a.scenic_season             AS scenic_season,\n" +
                "  a.scenic_grade              AS scenic_grade,\n" +
                "  a.scenic_createtime         AS createtime,\n" +
                "\n" +
                "  a.scenic_theme_encryptId    AS theme_encryptId,\n" +
                "  a.scenic_playType_encryptId AS palyType_encryptId,\n" +
                "  a.sceni_crowd_encryptId     AS crowd_encryptId,\n" +
                "\n" +
                "  a.scenic_bus_route            AS scenic_bus_route,\n" +
                "  a.scenic_self_route           AS scenic_self_route,\n" +
                "  a.scenic_Intr_info            AS scenic_Intr_info\n" +
                "FROM xw_scenic_basicinfo a\n" +
                "  LEFT JOIN xw_business_info b ON a.business_encryptId = b.business_encryptId\n" +
                "                                  AND a.scenic_encryptId = b.sec_encryptId ";
        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");

        //景区——查找其中一个店铺的基本信息
        if (!business_encryptId.equals("") && !sceinc_encryptId.equals("")) {
            sb.append(" and a.business_encryptId in('" + business_encryptId + "') ");
            sb.append(" and a.scenic_encryptId in('" + sceinc_encryptId + "') ");
        }

        try {
            sql = sql + " where " + sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
            result.put("status", 0);
            result.put("msg", "ok");


            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject object = new JSONObject();
                object.put("business_encryptId", (rs.getString("business_encryptId") == null ? "" : rs.getString("business_encryptId")));
                object.put("scenic_encryptId", (rs.getString("scenic_encryptId") == null ? "" : rs.getString("scenic_encryptId")));

                object.put("business_name", (rs.getString("business_name") == null ? "" : rs.getString("business_name")));
                object.put("scenictypeName", (rs.getString("scenictypeName") == null ? "" : rs.getString("scenictypeName")));
                object.put("scenicType_encryptId", (rs.getString("scenicType_encryptId") == null ? "" : rs.getString("scenicType_encryptId")));
                object.put("tel", (rs.getString("tel") == null ? "" : rs.getString("tel")));
                object.put("business_Legal", (rs.getString("business_Legal") == null ? "" : rs.getString("business_Legal")));
                object.put("CompanyName", rs.getString("CompanyName"));
                object.put("business_license", rs.getString("business_license"));
                object.put("business_Credit", (rs.getString("business_Credit") == null ? "" : rs.getString("business_Credit")));
                object.put("city", (rs.getString("city") == null ? "" : rs.getString("city")));
                object.put("district", (rs.getString("district") == null ? "" : rs.getString("district")));
                object.put("develop_time", (rs.getString("develop_time") == null ? "" : rs.getString("develop_time")));
                object.put("scenic_ticket", (rs.getString("scenic_ticket") == null ? "" : rs.getString("scenic_ticket")));
                object.put("scenic_address", (rs.getString("scenic_address") == null ? "" : rs.getString("scenic_address")));
                object.put("business_create", (rs.getString("business_create") == null ? "" : rs.getString("business_create")));

                object.put("scenic_views", (rs.getString("scenic_views") == null ? "" : rs.getString("scenic_views")));
                object.put("scenic_score", (rs.getString("scenic_score") == null ? "" : rs.getString("scenic_score")));
                object.put("scenic_season", (rs.getString("scenic_season") == null ? "" : rs.getString("scenic_season")));
                object.put("scenic_grade", (rs.getString("scenic_grade") == null ? "" : rs.getString("scenic_grade")));

                object.put("scenic_bus_route", (rs.getString("scenic_bus_route") == null ? "" : rs.getString("scenic_bus_route")));
                object.put("scenic_self_route", (rs.getString("scenic_self_route") == null ? "" : rs.getString("scenic_self_route")));
                object.put("scenic_Intr_info", (rs.getString("scenic_Intr_info") == null ? "" : rs.getString("scenic_Intr_info")));

                object.put("theme_encryptId", (rs.getString("theme_encryptId") == null ? "" : rs.getString("theme_encryptId")));
                object.put("palyType_encryptId", (rs.getString("palyType_encryptId") == null ? "" : rs.getString("palyType_encryptId")));
                object.put("crowd_encryptId", (rs.getString("crowd_encryptId") == null ? "" : rs.getString("crowd_encryptId")));

                object.put("createtime", DateUtil.DateToString(rs.getDate("createtime")));
                jsonArray.add(object);
            }
            result.put("list", jsonArray);


        }catch (Exception e){
            e.printStackTrace();
            result.put("status", 40002);
            result.put("msg", e);
        }

        return result;
    }



}
